package com.example.kingdeescan.utils;

import android.util.Log;

import com.example.kingdeescan.dao.FBiller;
import com.example.kingdeescan.dao.FDept;
import com.example.kingdeescan.dao.FEmp;
import com.example.kingdeescan.dao.FFManger;
import com.example.kingdeescan.dao.FOrg;
import com.example.kingdeescan.dao.SEOutStock;
import com.example.kingdeescan.dao.SEOutStockEntry;
import com.example.kingdeescan.dto.DocInfoDTO;
import com.example.kingdeescan.dto.SSCEntryDTO;
import com.example.kingdeescan.dto.SSCHeadDTO;
import com.example.kingdeescan.enums.DocType;

import java.math.BigDecimal;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Timestamp;
import java.util.ArrayList;
import java.util.List;

import lombok.SneakyThrows;

/**
 * author  : zly
 * date    : 2020/7/10 14:49
 * desc    : 数据库连接工具类
 */
public class DBUtil {
    public static final String TAG="DBUtil";

    private Connection conn=null;
    private PreparedStatement pstmt = null ;
    private ResultSet rs =null;

    /**
     * 连接数据库服务器
     * @throws Exception
     */
    public void getDBConn(DBConfig db) throws Exception  {
        try
        {
            //加载JDBC驱动
            Class.forName(db.getDriver());
            Log.d("DBUtil", "jdbc驱动加载成功");
            //连接数据库
            conn = DriverManager.getConnection(db.getSqlCon(), db.getDbUser(), db.getDbPwd());
            Log.d("DBUtil", "数据库连接成功");
        } catch (ClassNotFoundException e) {
            throw new ClassNotFoundException();
        } catch (SQLException e) {
            throw new SQLException();
        }catch (Exception e){
            throw new Exception();
        }
    }

    /**
     * 查询
     * @return
     */
    public String query() throws Exception {
        String result = "";
        try
        {
            String querySql = "SELECT TOP 1 FBillNo FROM SEOrder;";
            //预处理语句不能将动态值用于列名
            pstmt = conn.prepareStatement(querySql);
            rs = pstmt.executeQuery();
            while (rs.next())
            {
                String fBillNo = rs.getString("FBillNo");
                result =fBillNo;
            }
        } catch (SQLException e){
            throw new SQLException();
        }catch (Exception e){
            throw new Exception();
        } finally{
            release();
            return result;
        }
    }

    /**
     * 查询最大单据编号
     * @param docType
     * @param queryText
     * @return
     * @throws Exception
     */
    public String queryMaxFBillNo(DocType docType, String queryText) throws Exception{
        if (!isConnOpened()){
            throw new SQLException("未成功连接数据库");
        }
        String fBillNoMax="";//最大单据编号
        try
        {
            switch (docType){
                case DOC_TYPE_SSCK:
                    String querySql = "SELECT MAX(FBillNo) AS FBillNoMax FROM ICStockBill WHERE FTranType=21 AND FBillNo LIKE ?";
                    pstmt = conn.prepareStatement(querySql);
                    pstmt.setString(1, "%"+queryText+"%");
                    rs = pstmt.executeQuery();
                    while (rs.next())
                    {
                        fBillNoMax = rs.getString("FBillNoMax");
                    }
                    break;
            }
        } catch (SQLException e){
            throw new SQLException();
        }catch (Exception e){
            throw new Exception();
        } finally{
            release();
            return fBillNoMax;
        }

    }

    /**
     * 查询单据最大内码
     * @param docInfoDTO
     * @return
     * @throws Exception
     */
    public int queryMaxFInterID(DocInfoDTO docInfoDTO) throws Exception{
        if (!isConnOpened()){
            throw new SQLException("未成功连接数据库");
        }
        int fInterIDMax = 0;//单据编号内码
        try
        {
            switch (docInfoDTO.getDocType()){
                case DOC_TYPE_SSCK:
                    String querySql = "SELECT MAX(FInterID) AS FInterIDMax FROM ICStockBill";
                    pstmt = conn.prepareStatement(querySql);
                    rs = pstmt.executeQuery();
                    while (rs.next())
                    {
                        fInterIDMax = rs.getInt("FInterIDMax");
                    }
                    break;
            }
        } catch (SQLException e){
            throw new SQLException();
        }catch (Exception e){
            throw new Exception();
        } finally{
            release();
            return fInterIDMax;
        }

    }

    /**
     * 查询销售出库最大单据体编号
     * @param sscHeadDTO
     * @return
     * @throws Exception
     */
    public int querySSCMaxFEntryID(SSCHeadDTO sscHeadDTO) throws Exception{
        if (!isConnOpened()){
            throw new SQLException("未成功连接数据库");
        }
        int fEntryIDMax = 0;//最大单据体编号
        try
        {
            String querySql = "SELECT ISNULL(MAX(FEntryID)+1,1) AS FEntryIDMax FROM ICStockBillEntry " +
                    "WHERE FSourceTranType=21 AND FInterID=?";
            pstmt = conn.prepareStatement(querySql);
            pstmt.setInt(1,sscHeadDTO.getFInterID());
            rs = pstmt.executeQuery();
            while (rs.next())
            {
                fEntryIDMax = rs.getInt("FEntryIDMax");
            }
        } catch (SQLException e){
            throw new SQLException();
        }catch (Exception e){
            throw new Exception();
        } finally{
            release();
            return fEntryIDMax;
        }
    }

    /**
     * 查询购货单位
     * @return
     */
    public List<FOrg> queryFOrg(String queryText) throws Exception {
        if (!isConnOpened()){
            throw new SQLException("未成功连接数据库");
        }
        List<FOrg> fOrgList =new ArrayList<>();
        FOrg fOrg;
        PreparedStatement pstmt=null;
        ResultSet rs=null;
        try
        {
            String querySql = "SELECT FItemID,FName,FNumber FROM t_Organization WHERE FName LIKE ? OR FNumber LIKE ?";
            //预处理语句不能将动态值用于列名
            pstmt = conn.prepareStatement(querySql);
            pstmt.setString(1,"%"+queryText+"%");
            pstmt.setString(2,queryText+"%");
            rs = pstmt.executeQuery();
            while (rs.next())
            {
                int FItemID = rs.getInt("FItemID");
                String FNumber = rs.getString("FNumber");
                String FName = rs.getString("FName");

                fOrg =new FOrg();
                fOrg.setFItemID(FItemID);
                fOrg.setFNumber(FNumber);
                fOrg.setFName(FName);

                fOrgList.add(fOrg);
            }
        } catch (SQLException e){
            throw new SQLException();
        }catch (Exception e){
            throw new Exception();
        } finally{
            if (null!=pstmt && !pstmt.isClosed()){
                pstmt.close();
            }
            if (null!=rs && !rs.isClosed()){
                rs.close();
            }
//            release();
            return fOrgList;
        }
    }

    /**
     * 查询部门
     * @return
     */
    public List<FDept> queryFDept(String queryText) throws Exception {
        if (!isConnOpened()){
            throw new SQLException("未成功连接数据库");
        }
        List<FDept> fDeptList=new ArrayList<>();
        FDept fDept;
        PreparedStatement pstmt=null;
        ResultSet rs=null;
        try
        {
            String querySql = "SELECT FItemID,FNumber,FName FROM t_Department WHERE FName LIKE ? OR FNumber LIKE ?";
            //预处理语句不能将动态值用于列名
            pstmt = conn.prepareStatement(querySql);
            pstmt.setString(1,"%"+queryText+"%");
            pstmt.setString(2,queryText+"%");
            rs = pstmt.executeQuery();
            while (rs.next())
            {
                int FItemID = rs.getInt("FItemID");
                String FNumber = rs.getString("FNumber");
                String FName = rs.getString("FName");

                fDept=new FDept();
                fDept.setFItemID(FItemID);
                fDept.setFNumber(FNumber);
                fDept.setFName(FName);

                fDeptList.add(fDept);
            }
        } catch (SQLException e){
            throw new SQLException();
        }catch (Exception e){
            throw new Exception();
        } finally{
            if (null!=pstmt && !pstmt.isClosed()){
                pstmt.close();
            }
            if (null!=rs && !rs.isClosed()){
                rs.close();
            }
//            release();
            return fDeptList;
        }
    }

    /**
     * 查询发货员
     * @return
     */
    public List<FFManger> queryFFManger(String queryText) throws Exception {
        if (!isConnOpened()){
            throw new SQLException("未成功连接数据库");
        }
        List<FFManger> ffMangerList=new ArrayList<>();
        FFManger ffManger;
        PreparedStatement pstmt=null;
        ResultSet rs=null;
        try
        {
            String querySql = "SELECT FItemID,FName,FDepartmentID,FNumber FROM t_Base_Emp WHERE FName LIKE ? OR FNumber LIKE ?";
            //预处理语句不能将动态值用于列名
            pstmt = conn.prepareStatement(querySql);
            pstmt.setString(1,"%"+queryText+"%");
            pstmt.setString(2,queryText+"%");
            rs = pstmt.executeQuery();
            while (rs.next())
            {
                int FItemID = rs.getInt("FItemID");
                int FDepartmentID = rs.getInt("FDepartmentID");
                String FNumber = rs.getString("FNumber");
                String FName = rs.getString("FName");

                ffManger=new FFManger();
                ffManger.setFItemID(FItemID);
                ffManger.setFDepartmentID(FDepartmentID);
                ffManger.setFNumber(FNumber);
                ffManger.setFName(FName);

                ffMangerList.add(ffManger);
            }
        } catch (SQLException e){
            throw new SQLException();
        }catch (Exception e){
            throw new Exception();
        } finally{
            if (null!=pstmt && !pstmt.isClosed()){
                pstmt.close();
            }
            if (null!=rs && !rs.isClosed()){
                rs.close();
            }
//            release();
            return ffMangerList;
        }
    }

    /**
     * 查询业务员
     * @return
     */
    public List<FEmp> queryFEmp(String queryText) throws Exception {
        if (!isConnOpened()){
            throw new SQLException("未成功连接数据库");
        }
        List<FEmp> fEmpList=new ArrayList<>();
        FEmp fEmp;
        PreparedStatement pstmt=null;
        ResultSet rs=null;
        try
        {
            String querySql = "SELECT FItemID,FName,FDepartmentID,FNumber FROM t_Base_Emp WHERE FName LIKE ? OR FNumber LIKE ?";
            //预处理语句不能将动态值用于列名
            pstmt = conn.prepareStatement(querySql);
            pstmt.setString(1,"%"+queryText+"%");
            pstmt.setString(2,queryText+"%");
            rs = pstmt.executeQuery();
            while (rs.next())
            {
                int FItemID = rs.getInt("FItemID");
                int FDepartmentID = rs.getInt("FDepartmentID");
                String FNumber = rs.getString("FNumber");
                String FName = rs.getString("FName");

                fEmp=new FEmp();
                fEmp.setFItemID(FItemID);
                fEmp.setFDepartmentID(FDepartmentID);
                fEmp.setFNumber(FNumber);
                fEmp.setFName(FName);

                fEmpList.add(fEmp);
            }
        } catch (SQLException e){
            throw new SQLException();
        }catch (Exception e){
            throw new Exception();
        } finally{
            if (null!=pstmt && !pstmt.isClosed()){
                pstmt.close();
            }
            if (null!=rs && !rs.isClosed()){
                rs.close();
            }
//            release();
            return fEmpList;
        }
    }

    /**
     * 查询制单员
     * @return
     */
    public List<FBiller> queryFBiller(String queryText) throws Exception {
        if (!isConnOpened()){
            throw new SQLException("未成功连接数据库");
        }
        List<FBiller> fBillerList=new ArrayList<>();
        FBiller fBiller;
        PreparedStatement pstmt=null;
        ResultSet rs=null;
        try
        {
            String querySql = "SELECT FUserID,FName FROM t_user WHERE FName LIKE ? OR FUserID LIKE ?";
            //预处理语句不能将动态值用于列名
            pstmt = conn.prepareStatement(querySql);
            pstmt.setString(1,"%"+queryText+"%");
            pstmt.setString(2,"%"+queryText+"%");
            rs = pstmt.executeQuery();
            while (rs.next())
            {
                int FUserID = rs.getInt("FUserID");
                String FName = rs.getString("FName");

                fBiller=new FBiller();
                fBiller.setFUserID(FUserID);
                fBiller.setFName(FName);

                fBillerList.add(fBiller);
            }
        } catch (SQLException e){
            throw new SQLException();
        }catch (Exception e){
            throw new Exception();
        } finally{
            if (null!=pstmt && !pstmt.isClosed()){
                pstmt.close();
            }
            if (null!=rs && !rs.isClosed()){
                rs.close();
            }
//            release();
            return fBillerList;
        }
    }

    /**
     * 查询发货通知单
     * @return
     */
    public List<SEOutStock> querySelBillNo(int fCustID) throws Exception {
        if (!isConnOpened()){
            throw new SQLException("未成功连接数据库");
        }
        List<SEOutStock> seOutStockList=new ArrayList<>();
        SEOutStock seOutStock;
        SEOutStockEntry seOutStockEntry;
        List<SEOutStockEntry> seOutStockEntryList=new ArrayList<>();
        PreparedStatement pstmtSSE=null;
        ResultSet rsSSE=null;
        PreparedStatement pstmt=null;
        ResultSet rs=null;
        try
        {
            /** 发货通知单
             SELECT FInterID,FBillNo,FDate,FCustID,org.FName,FHeadSelfS0238
             FROM SEOutStock se
             LEFT JOIN t_Organization org
             ON org.FItemID=se.FCustID
             WHERE FCustID='919'
             */
            String querySEOutStockSql = "SELECT FInterID,FBillNo,FDate,FCustID,org.FName as FCustName,FHeadSelfS0238 " +
                                "FROM SEOutStock se " +
                                "LEFT JOIN t_Organization org " +
                                "ON org.FItemID=se.FCustID " +
                                "WHERE FCustID=?";
            //预处理语句不能将动态值用于列名
            pstmt = conn.prepareStatement(querySEOutStockSql);
            pstmt.setInt(1,fCustID);
            rs = pstmt.executeQuery();
            while (rs.next())
            {
                int FInterID = rs.getInt("FInterID");
                String FBillNo = rs.getString("FBillNo");
                Timestamp FDate = rs.getTimestamp("FDate");
                int FCustID = rs.getInt("FCustID");
                String FCustName = rs.getString("FCustName");
                String FHeadSelfS0238 = rs.getString("FHeadSelfS0238");

                seOutStock=new SEOutStock();
                seOutStock.setFInterID(FInterID);
                seOutStock.setFBillNo(FBillNo);
                seOutStock.setFDate(FDate);
                seOutStock.setFCustID(FCustID);
                seOutStock.setFCustName(FCustName);
                seOutStock.setFHeadSelfS0238(FHeadSelfS0238);

                //根据主表的FInterID查询分录的数据，并添加到seOutStockList的分录字段
                /** 发货通知单分录230007
                 SELECT sse.FItemID,ic.FNumber AS FItemNumber,ic.FName AS FItemName,ic.FModel,FAuxPropID,ai.FName AS FAuxPropName,sse.FUnitID,mu.FNumber AS FUnitNumber,mu.FName AS FUnitName,FAuxQty,FPrice,FAmount,FSourceBillNo,FMtoNo,FFetchDate
                 FROM SEOutStockEntry sse
                 LEFT JOIN t_ICItem ic ON ic.FItemID=sse.FItemID
                 LEFT JOIN t_AuxItem ai ON ai.FItemID=sse.FAuxPropID
                 LEFT JOIN T_MeasureUnit mu ON mu.FMeasureUnitID=sse.FUnitID
                 WHERE sse.FInterID='1725'
                 */
                String querySEOutStockEntrySql="SELECT sse.FItemID,ic.FNumber AS FItemNumber,ic.FName AS FItemName,ic.FModel,FAuxPropID,ai.FName AS FAuxPropName,sse.FUnitID,mu.FNumber AS FUnitNumber,mu.FName AS FUnitName," +
                        "FAuxQty,FPrice,FAmount,FSourceBillNo,FMtoNo,FFetchDate " +
                        "FROM SEOutStockEntry sse " +
                        "LEFT JOIN t_ICItem ic ON ic.FItemID=sse.FItemID " +
                        "LEFT JOIN t_AuxItem ai ON ai.FItemID=sse.FAuxPropID " +
                        "LEFT JOIN T_MeasureUnit mu ON mu.FMeasureUnitID=sse.FUnitID " +
                        "WHERE sse.FInterID=?";
                //预处理语句不能将动态值用于列名

                pstmtSSE = conn.prepareStatement(querySEOutStockEntrySql);
                pstmtSSE.setInt(1,FInterID);
                rsSSE = pstmtSSE.executeQuery();
                while (rsSSE.next()){
                    int FItemID = rsSSE.getInt("FItemID");
                    String FItemNumber = rsSSE.getString("FItemNumber");
                    String FItemName = rsSSE.getString("FItemName");
                    String FModel = rsSSE.getString("FModel");
                    int FAuxPropID = rsSSE.getInt("FAuxPropID");
                    String FAuxPropName = rsSSE.getString("FAuxPropName");
                    int FUnitID = rsSSE.getInt("FUnitID");
                    String FUnitNumber = rsSSE.getString("FUnitNumber");
                    String FUnitName = rsSSE.getString("FUnitName");
                    float FAuxQty = rsSSE.getFloat("FAuxQty");
                    float FPrice = rsSSE.getFloat("FPrice");
                    float FAmount = rsSSE.getFloat("FAmount");
                    String FSourceBillNo = rsSSE.getString("FSourceBillNo");
                    String FMtoNo = rsSSE.getString("FMtoNo");
                    Timestamp FFetchDate = rsSSE.getTimestamp("FFetchDate");

                    seOutStockEntry=new SEOutStockEntry();
                    seOutStockEntry.setFItemID(FItemID);
                    seOutStockEntry.setFItemNumber(FItemNumber);
                    seOutStockEntry.setFItemName(FItemName);
                    seOutStockEntry.setFModel(FModel);
                    seOutStockEntry.setFAuxPropID(FAuxPropID);
                    seOutStockEntry.setFAuxPropName(FAuxPropName);
                    seOutStockEntry.setFUnitID(FUnitID);
                    seOutStockEntry.setFUnitNumber(FUnitNumber);
                    seOutStockEntry.setFUnitName(FUnitName);
                    seOutStockEntry.setFAuxQty(FAuxQty);
                    seOutStockEntry.setFPrice(FPrice);
                    seOutStockEntry.setFAmount(FAmount);
                    seOutStockEntry.setFSourceBillNo(FSourceBillNo);
                    seOutStockEntry.setFMtoNo(FMtoNo);
                    seOutStockEntry.setFFetchDate(FFetchDate);

                    seOutStockEntryList.add(seOutStockEntry);
                }
                seOutStock.setSeOutStockEntryList(seOutStockEntryList);

                seOutStockList.add(seOutStock);
            }
        } catch (SQLException e){
            throw new SQLException();
        }catch (Exception e){
            throw new Exception();
        } finally{
            if (null!=pstmt && !pstmt.isClosed()){
                pstmt.close();
            }
            if (null!=rs && !rs.isClosed()){
                rs.close();
            }
            if (null!=pstmtSSE && !pstmtSSE.isClosed()){
                pstmtSSE.close();
            }
            if (null!=rsSSE && !rsSSE.isClosed()){
                rsSSE.close();
            }
//            release();
            return seOutStockList;
        }
    }

    /**
     * 查询发货通知单(模糊)
     * @return
     */
    public List<SEOutStock> querySelBillNo(int fCustID,String queryText) throws Exception {
        if (!isConnOpened()){
            throw new SQLException("未成功连接数据库");
        }
        List<SEOutStock> seOutStockList=new ArrayList<>();
        SEOutStock seOutStock;
        SEOutStockEntry seOutStockEntry;
        List<SEOutStockEntry> seOutStockEntryList=new ArrayList<>();
        PreparedStatement pstmtSSE=null;
        ResultSet rsSSE=null;
        PreparedStatement pstmt=null;
        ResultSet rs=null;
        try
        {
            /** 发货通知单
             SELECT FInterID,FBillNo,FDate,FCustID,org.FName,FHeadSelfS0238
             FROM SEOutStock se
             LEFT JOIN t_Organization org
             ON org.FItemID=se.FCustID
             WHERE FCustID='919' AND (FBillNo LIKE '%FBillNo%' OR FHeadSelfS0238 LIKE '%FHeadSelfS0238%')
             */
            String querySEOutStockSql = "SELECT FInterID,FBillNo,FDate,FCustID,org.FName as FCustName,FHeadSelfS0238 " +
                    "FROM SEOutStock se " +
                    "LEFT JOIN t_Organization org " +
                    "ON org.FItemID=se.FCustID " +
                    "WHERE FCustID=? AND (FBillNo LIKE ? OR FHeadSelfS0238 LIKE ?)";
            //预处理语句不能将动态值用于列名
            pstmt = conn.prepareStatement(querySEOutStockSql);
            pstmt.setInt(1,fCustID);
            pstmt.setString(2,"%"+queryText+"%");
            pstmt.setString(3,"%"+queryText+"%");
            rs = pstmt.executeQuery();
            while (rs.next())
            {
                int FInterID = rs.getInt("FInterID");
                String FBillNo = rs.getString("FBillNo");
                Timestamp FDate = rs.getTimestamp("FDate");
                int FCustID = rs.getInt("FCustID");
                String FCustName = rs.getString("FCustName");
                String FHeadSelfS0238 = rs.getString("FHeadSelfS0238");

                seOutStock=new SEOutStock();
                seOutStock.setFInterID(FInterID);
                seOutStock.setFBillNo(FBillNo);
                seOutStock.setFDate(FDate);
                seOutStock.setFCustID(FCustID);
                seOutStock.setFCustName(FCustName);
                seOutStock.setFHeadSelfS0238(FHeadSelfS0238);

                //根据主表的FInterID查询分录的数据，并添加到seOutStockList的分录字段
                /** 发货通知单分录230007
                 SELECT sse.FItemID,ic.FNumber AS FItemNumber,ic.FName AS FItemName,ic.FModel,FAuxPropID,ai.FName AS FAuxPropName,sse.FUnitID,mu.FNumber AS FUnitNumber,mu.FName AS FUnitName,FAuxQty,FPrice,FAmount,FSourceBillNo,FMtoNo,FFetchDate
                 FROM SEOutStockEntry sse
                 LEFT JOIN t_ICItem ic ON ic.FItemID=sse.FItemID
                 LEFT JOIN t_AuxItem ai ON ai.FItemID=sse.FAuxPropID
                 LEFT JOIN T_MeasureUnit mu ON mu.FMeasureUnitID=sse.FUnitID
                 WHERE sse.FInterID='1725'
                 */
                String querySEOutStockEntrySql="SELECT sse.FItemID,ic.FNumber AS FItemNumber,ic.FName AS FItemName,ic.FModel,FAuxPropID,ai.FName AS FAuxPropName,sse.FUnitID,mu.FNumber AS FUnitNumber,mu.FName AS FUnitName," +
                        "FAuxQty,FPrice,FAmount,FSourceBillNo,FMtoNo,FFetchDate " +
                        "FROM SEOutStockEntry sse " +
                        "LEFT JOIN t_ICItem ic ON ic.FItemID=sse.FItemID " +
                        "LEFT JOIN t_AuxItem ai ON ai.FItemID=sse.FAuxPropID " +
                        "LEFT JOIN T_MeasureUnit mu ON mu.FMeasureUnitID=sse.FUnitID " +
                        "WHERE sse.FInterID=?";
                //预处理语句不能将动态值用于列名

                pstmtSSE = conn.prepareStatement(querySEOutStockEntrySql);
                pstmtSSE.setInt(1,FInterID);
                rsSSE = pstmtSSE.executeQuery();
                while (rsSSE.next()){
                    int FItemID = rsSSE.getInt("FItemID");
                    String FItemNumber = rsSSE.getString("FItemNumber");
                    String FItemName = rsSSE.getString("FItemName");
                    String FModel = rsSSE.getString("FModel");
                    int FAuxPropID = rsSSE.getInt("FAuxPropID");
                    String FAuxPropName = rsSSE.getString("FAuxPropName");
                    int FUnitID = rsSSE.getInt("FUnitID");
                    String FUnitNumber = rsSSE.getString("FUnitNumber");
                    String FUnitName = rsSSE.getString("FUnitName");
                    float FAuxQty = rsSSE.getFloat("FAuxQty");
                    float FPrice = rsSSE.getFloat("FPrice");
                    float FAmount = rsSSE.getFloat("FAmount");
                    String FSourceBillNo = rsSSE.getString("FSourceBillNo");
                    String FMtoNo = rsSSE.getString("FMtoNo");
                    Timestamp FFetchDate = rsSSE.getTimestamp("FFetchDate");

                    seOutStockEntry=new SEOutStockEntry();
                    seOutStockEntry.setFItemID(FItemID);
                    seOutStockEntry.setFItemNumber(FItemNumber);
                    seOutStockEntry.setFItemName(FItemName);
                    seOutStockEntry.setFModel(FModel);
                    seOutStockEntry.setFAuxPropID(FAuxPropID);
                    seOutStockEntry.setFAuxPropName(FAuxPropName);
                    seOutStockEntry.setFUnitID(FUnitID);
                    seOutStockEntry.setFUnitNumber(FUnitNumber);
                    seOutStockEntry.setFUnitName(FUnitName);
                    seOutStockEntry.setFAuxQty(FAuxQty);
                    seOutStockEntry.setFPrice(FPrice);
                    seOutStockEntry.setFAmount(FAmount);
                    seOutStockEntry.setFSourceBillNo(FSourceBillNo);
                    seOutStockEntry.setFMtoNo(FMtoNo);
                    seOutStockEntry.setFFetchDate(FFetchDate);

                    seOutStockEntryList.add(seOutStockEntry);
                }
                seOutStock.setSeOutStockEntryList(seOutStockEntryList);

                seOutStockList.add(seOutStock);
            }
        } catch (SQLException e){
            throw new SQLException();
        }catch (Exception e){
            throw new Exception();
        } finally{
            if (null!=pstmt && !pstmt.isClosed()){
                pstmt.close();
            }
            if (null!=rs && !rs.isClosed()){
                rs.close();
            }
            if (null!=pstmtSSE && !pstmtSSE.isClosed()){
                pstmtSSE.close();
            }
            if (null!=rsSSE && !rsSSE.isClosed()){
                rsSSE.close();
            }
            return seOutStockList;
        }
    }

    /**
     * 添加
     * @throws Exception
     */
    public void insert() throws Exception{
        try {
            String insertSql = "";
            pstmt = conn.prepareStatement(insertSql);
            pstmt.executeUpdate();
        }catch (Exception e){
             throw new Exception("插入异常");
        }finally {
            release();
        }
    }

    /**
     * 删除
     * @throws Exception
     */
    public void delete() throws Exception {
        String deleteSql = "" ;
        try {
            pstmt = conn.prepareStatement(deleteSql) ;
            pstmt.executeUpdate() ;
        } catch (Exception e) {
            throw new Exception("删除异常") ;
        }finally {
            release();
        }
    }

    /**
     * 修改
     * @throws Exception
     */
    public void update() throws Exception {
        String updateSql = "";
        try {
            pstmt = conn.prepareStatement(updateSql);
            pstmt.executeUpdate();
            pstmt.close();
        } catch (Exception e) {
            throw new Exception("更新异常");
        }finally {
            release();
        }
    }

    /**
     * 校验购货单位
     * @param forg
     * @return
     */
    public boolean validFOrg(String forg) throws Exception{
        if (!isConnOpened()){
            throw new SQLException("未成功连接数据库");
        }
        boolean validFlag=false;
        try
        {
            String querySql = "SELECT * FROM t_Organization WHERE FName = ?";
            //预处理语句不能将动态值用于列名
            pstmt = conn.prepareStatement(querySql);
            pstmt.setString(1,forg);
            rs = pstmt.executeQuery();
            if (rs.next()){
                validFlag=true;
            }
        } catch (SQLException e){
            throw new SQLException();
        }catch (Exception e){
            throw new Exception();
        } finally{
            release();
            return validFlag;
        }
    }

    /**
     * 校验选单号
     * @param split
     * @return
     */
    public boolean validSelBillNo(String[] split) throws Exception {
        if (!isConnOpened()){
            throw new SQLException("未成功连接数据库");
        }
        boolean validFlag=false;
        try
        {
            String querySql = "SELECT * FROM SEOutStock WHERE FBillNo = ?";
            for (int i = 0; i < split.length; i++) {
                //预处理语句不能将动态值用于列名
                pstmt = conn.prepareStatement(querySql);
                pstmt.setString(1,split[i]);
                rs = pstmt.executeQuery();
                if (rs.next()){
                    validFlag=true;
                }else{
                    validFlag=false;
                }
            }
        } catch (SQLException e){
            throw new SQLException();
        }catch (Exception e){
            throw new Exception();
        } finally{
            release();
            return validFlag;
        }
    }

    /**
     * 校验部门
     * @param fdept
     * @return
     */
    public boolean validFDept(String fdept) throws Exception {
        if (!isConnOpened()){
            throw new SQLException("未成功连接数据库");
        }
        boolean validFlag=false;
        try
        {
            String querySql = "SELECT * FROM t_Department WHERE FName = ?";
            //预处理语句不能将动态值用于列名
            pstmt = conn.prepareStatement(querySql);
            pstmt.setString(1,fdept);
            rs = pstmt.executeQuery();
            if (rs.next()){
                validFlag=true;
            }
        } catch (SQLException e){
            throw new SQLException();
        }catch (Exception e){
            throw new Exception();
        } finally{
            release();
            return validFlag;
        }
    }

    /**
     * 校验业务员
     * @param femp
     * @return
     */
    public boolean validFEmp(String femp) throws Exception{
        if (!isConnOpened()){
            throw new SQLException("未成功连接数据库");
        }
        boolean validFlag=false;
        try
        {
            String querySql = "SELECT * FROM T_Emp WHERE FName = ?";
            //预处理语句不能将动态值用于列名
            pstmt = conn.prepareStatement(querySql);
            pstmt.setString(1,femp);
            rs = pstmt.executeQuery();
            if (rs.next()){
                validFlag=true;
            }
        } catch (SQLException e){
            throw new SQLException();
        }catch (Exception e){
            throw new Exception();
        } finally{
            release();
            return validFlag;
        }
    }

    /**
     * 校验发货员
     * @param ffmanger
     * @return
     */
    public boolean validFFManger(String ffmanger) throws Exception{
        if (!isConnOpened()){
            throw new SQLException("未成功连接数据库");
        }
        boolean validFlag=false;
        try
        {
            String querySql = "SELECT * FROM T_Emp WHERE FName = ?";
            //预处理语句不能将动态值用于列名
            pstmt = conn.prepareStatement(querySql);
            pstmt.setString(1,ffmanger);
            rs = pstmt.executeQuery();
            if (rs.next()){
                validFlag=true;
            }
        } catch (SQLException e){
            throw new SQLException();
        }catch (Exception e){
            throw new Exception();
        } finally{
            release();
            return validFlag;
        }
    }

    /**
     * 校验制单员
     * @param fbiller
     * @return
     */
    public boolean validFBiller(String fbiller) throws Exception{
        if (!isConnOpened()){
            throw new SQLException("未成功连接数据库");
        }
        boolean validFlag=false;
        try
        {
            String querySql = "SELECT * FROM t_user WHERE FName = ?";
            //预处理语句不能将动态值用于列名
            pstmt = conn.prepareStatement(querySql);
            pstmt.setString(1,fbiller);
            rs = pstmt.executeQuery();
            if (rs.next()){
                validFlag=true;
            }
        } catch (SQLException e){
            throw new SQLException();
        }catch (Exception e){
            throw new Exception();
        } finally{
            release();
            return validFlag;
        }
    }

    /**
     * 插入销售出库单
     * @param sscHeadDTO
     */
    public boolean insertSSCK(SSCHeadDTO sscHeadDTO) throws Exception{
        if (!isConnOpened()){
            throw new SQLException("未成功连接数据库");
        }

        PreparedStatement pstmtHead=null;
        PreparedStatement pstmtQuery=null;
        PreparedStatement pstmtEntry=null;
        ResultSet rs=null;
        boolean insertFlag=true;
        int insertStatus;
        try
        {
            //插入销售出库单据头
            String insertHeadSql = "INSERT INTO " +
                    "ICStockBill(FBrNo,FInterID,FTranType,FDate,FBillNo," +
                    "FDeptID,FEmpID,FSupplyID,FFManagerID,FBillerID,FHeadSelfB0164) " +
                    "VALUES(?,?,?,GETDATE(),?,?,?,?,?,?,?)";
            pstmtHead = conn.prepareStatement(insertHeadSql);
            pstmtHead.setString(1,sscHeadDTO.getFBrNo());
            pstmtHead.setInt(2,sscHeadDTO.getFInterID());
            pstmtHead.setInt(3,sscHeadDTO.getFTranType());
            pstmtHead.setString(4,sscHeadDTO.getFBillNo());
            pstmtHead.setInt(5,sscHeadDTO.getFDeptID());
            pstmtHead.setInt(6,sscHeadDTO.getFEmpID());
            pstmtHead.setInt(7,sscHeadDTO.getFSupplyID());
            pstmtHead.setInt(8,sscHeadDTO.getFFManagerID());
            pstmtHead.setInt(9,sscHeadDTO.getFBillerID());
            pstmtHead.setString(10,sscHeadDTO.getFHeadSelfB0164());

            insertStatus = pstmtHead.executeUpdate();
            if (insertStatus<=0){
                return false;
            }

            //插入销售出库单据体
            int fEntryIDMax = 0;//最大单据体编号
            String queryFEntryIDMax="SELECT ISNULL(MAX(FEntryID)+1,1) AS FEntryIDMax FROM ICStockBillEntry " +
                    "WHERE FSourceTranType=21 AND FInterID=?";
            pstmtQuery = conn.prepareStatement(queryFEntryIDMax);
            pstmtQuery.setInt(1,sscHeadDTO.getFInterID());

            String insertEntrySql="INSERT INTO ICStockBillEntry(FBrNo,FInterID,FEntryID,FItemID,FQtyMust," +
                    "FQty,FPrice,FAmount,FUnitID,FAuxPrice,FAuxQty,FAuxQtyMust,FSourceEntryID,FAuxPropID," +
                    "FSourceTranType,FSourceInterId,FSourceBillNo,FClientOrderNo) " +
                    "VALUES(?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)";
            pstmtEntry = conn.prepareStatement(insertEntrySql);
            List<SSCEntryDTO> sscEntryDTOList = sscHeadDTO.getSscEntryDTOList();
            for (int i = 0; i < sscEntryDTOList.size(); i++) {
                rs = pstmtQuery.executeQuery();
                while (rs.next())
                {
                    fEntryIDMax = rs.getInt("FEntryIDMax");
                }

                String fBrNo = sscEntryDTOList.get(i).getFBrNo();
                int fInterID = sscEntryDTOList.get(i).getFInterID();
//                int fEntryID = sscEntryDTOList.get(i).getFEntryID();
                int fItemID = sscEntryDTOList.get(i).getFItemID();
                float fQtyMust = sscEntryDTOList.get(i).getFQtyMust();
                float fQty = sscEntryDTOList.get(i).getFQty();
                float fPrice = sscEntryDTOList.get(i).getFPrice();
                float fAmount = sscEntryDTOList.get(i).getFAmount();
                int fUnitID = sscEntryDTOList.get(i).getFUnitID();
                float fAuxPrice = sscEntryDTOList.get(i).getFAuxPrice();
                float fAuxQty = sscEntryDTOList.get(i).getFAuxQty();
                float fAuxQtyMust = sscEntryDTOList.get(i).getFAuxQtyMust();
                int fAuxPropID = sscEntryDTOList.get(i).getFAuxPropID();
                int fSourceTranType = sscEntryDTOList.get(i).getFSourceTranType();
                String fSourceBillNo = sscEntryDTOList.get(i).getFSourceBillNo();
                String fClientOrderNo = sscEntryDTOList.get(i).getFClientOrderNo();

                pstmtEntry.setString(1, fBrNo);
                pstmtEntry.setInt(2, fInterID);
                pstmtEntry.setInt(3, fEntryIDMax);
                pstmtEntry.setInt(4, fItemID);
                pstmtEntry.setFloat(5, fQtyMust);
                pstmtEntry.setFloat(6, fQty);
                pstmtEntry.setFloat(7, fPrice);
                pstmtEntry.setFloat(8, fAmount);
                pstmtEntry.setInt(9, fUnitID);
                pstmtEntry.setFloat(10, fAuxPrice);
                pstmtEntry.setFloat(11, fAuxQty);
                pstmtEntry.setFloat(12, fAuxQtyMust);
                pstmtEntry.setInt(13, fEntryIDMax);
                pstmtEntry.setInt(14, fAuxPropID);
                pstmtEntry.setInt(15, fSourceTranType);
                pstmtEntry.setInt(16, fInterID);
                pstmtEntry.setString(17, fSourceBillNo);
                pstmtEntry.setString(18, fClientOrderNo);

                insertStatus = pstmtEntry.executeUpdate();
                if (insertStatus<=0){
                    insertFlag=false;
                    break;
                }
            }

            return insertFlag;
        } catch (SQLException e){
            e.printStackTrace();
            throw e;
        }catch (Exception e){
            e.printStackTrace();
            throw e;
        } finally{
            if (null!=pstmtHead && !pstmtHead.isClosed()){
                pstmtHead.close();
            }
            if (null!=pstmtEntry && !pstmtEntry.isClosed()){
                pstmtEntry.close();
            }
            if (null!=pstmtQuery && !pstmtQuery.isClosed()){
                pstmtQuery.close();
            }
            if (null!=rs && !rs.isClosed()){
                rs.close();
            }
        }
    }

    /**
     * 判断数据库连接是否正常
     * @return
     * @throws SQLException
     */
    public boolean isConnOpened() throws SQLException {
        if (null!=conn && !conn.isClosed()){
            return true;
        }
        return false;
    }

    /**
     * 释放资源
     */
    public void release() throws SQLException {
//        if (isConnOpened()){
//            conn.close();
//        }
        if (null!= pstmt && !pstmt.isClosed()){
            pstmt.close();
        }
        if (null!= rs && !rs.isClosed()){
            rs.close();
        }
    }
}
